-------------------------------------------------------------
-- @name: view_transformation
-- @author: dion cho
-- @description: test for view transformation
--------------------------------------------------------------



@off_cbqt

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 char(10), c3 int);
create table t2(c1 int, c2 char(10), c3 int);
create table t3(c1 int, c2 char(10), c3 int);


create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

create index t1_n2 on t1(c3);
create index t2_n2 on t2(c3);
create index t3_n2 on t3(c3);

insert into t1 
select level, 'dummy', mod(level, 100) from dual
connect by level <= 10000
;

insert into t2
select level, 'dummy', mod(level, 100)  from dual
connect by level <= 1000
;

insert into t3
select level, 'dummy', mod(level, 100) from dual
connect by level <= 100
;


commit;

@gather t1
@gather t2
@gather t3


-- simple view merging
-- when not merged
select count(*) 
from (
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ no_merge */ c1, c2 
    from t2 
    where c1 between 1 and 1000) v
where
  t1.c1 = v.c1
)
; 

@stat


-- when merged
select count(*)
from (
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c2 
    from t2 
    where c1 between 1 and 1000) v
where
  t1.c1 = v.c1
)
; 

@stat


-- complex view merge
-- when not merged
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ no_merge */
        c1, count(*) as c2
    from t2
    group by c1) v
where
  t1.c1 = v.c1 
; 

@stat


-- when merged
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ merge */
        c1, count(*) as c2
    from t2
    group by c1) v
where
  t1.c1 = v.c1 
; 

@stat



-- non mergeable views
-- see that predicate is pushed?
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c2, c3 from t2
    union all
   select c1, c2, c3 from t3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not mergeable
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, max(c2) as c2, max(c3) as c3
    from t2
    group by c1) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not mergeable
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select rownum as r, c1, c2, c3
    from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not mergeable
select /*+ gather_plan_statistics */
  t1.c1, v.c2, 
  cursor(select * from t3 where t3.c1 = t1.c1) as csr
from
  t1, 
  (select c1, c2, c3
    from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not mergeable
select /*+ gather_plan_statistics */
  t1.c1, v.*
from
  t1, 
  (select row_number() over (order by c1) as rn, 
          c1, c2, c3
  from t2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not mergeable
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat

-- how about this
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ merge */ c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- but this is mergeable. why the difference?
-- same even with cbqt off. 10053 tells the story
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c3, count(*) as c2
    from t2
    group by c1, c3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat



-- prediate pushing
-- simple filter push down
select /*+ gather_plan_statistics */
  *
from (
  select c1, c2 from t1
  union all
  select c1, c2 from t2
) v
where c1 = 1;

@stat


select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select c1, c2 from t2
    union all
   select c1, c2 from t3) v
where
  t1.c1 = v.c1 and
  v.c1 = 1
; 

@stat


-- simple predicate pushing is always activated
select /*+ gather_plan_statistics 
          no_push_pred(v) */ 
  *
from (
  select c1, c2 from t1
  union all
  select c1, c2 from t2
) v
where c1 = 1;

@stat


-- simple predicate pushing = predicate move around?
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select t2.c1 as c1, t2.c2 as c2, t2.c3 as c3,
        max(t3.c3) as max_key
    from t2, t3
    where t2.c1 = t3.c1
    group by t2.c1, t2.c2, t2.c3
    order by t2.c1, t2.c2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat

-- simple predicate pushing = predicate move around?
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select /*+ no_push_pred */ t2.c1 as c1, t2.c2 as c2, t2.c3 as c3,
        max(t3.c3) as max_key
    from t2, t3
    where t2.c1 = t3.c1
    group by t2.c1, t2.c2, t2.c3
    order by t2.c1, t2.c2) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


select /*+ gather_plan_statistics no_push_pred(v) */
  t3.c1, v.c2
from
  t3, 
  (select t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
    from t1, t2
    where t1.c1 = t2.c1
    order by t1.c1, t2.c2) v
where
  t3.c1 = v.c1(+) and
  t3.c3 = 1
; 

@stat


-- join predicate pushing
select /*+ gather_plan_statistics */
  t3.c1, v.c2
from
  t3, 
  (select t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
    from t1, t2
    where t1.c1 = t2.c1
    order by t1.c1, t2.c2) v
where
  t3.c1 = v.c1(+) and
  t3.c3 = 1
; 

@stat


select /*+ gather_plan_statistics */
  v.c1, v.c2, t2.c2
from
(
  select c1, c2 from t1 where c2 = 1
  union all
  select c1, c2 from t1 where c3 = 2
) v, t2 
where 
  t2.c1 = v.c1 and
  t2.c3 = 1
;

@stat


-- this is not join predicate-pushable
select /*+ gather_plan_statistics  */
  t3.c1, v.c2
from
  t3, 
  (select t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
    from t1, t2
    where t1.c1 = t2.c1
    order by t1.c1, t2.c2) v
where
  t3.c1 = v.c1 and  
  t3.c3 = 1
; 

@stat


-- and this is not join predicate-pushable
select /*+ gather_plan_statistics */
  t1.c1, v.c2
from
  t1, 
  (select t2.c1 as c1, t2.c2 as c2, t2.c3 as c3,
        max(t3.c3) as max_key
    from t2, t3
    where t2.c1 = t3.c1
    group by t2.c1, t2.c2, t2.c3  -- group by!!!
    order by t2.c1, t2.c2) v      
where
  t1.c1 = v.c1(+) and
  t1.c3 = 1
; 

@stat


-- even with push_pred hint
select /*+ gather_plan_statistics push_pred(v) */
  t1.c1, v.c2
from
  t1, 
  (select t2.c1 as c1, t2.c2 as c2, t2.c3 as c3,
        max(t3.c3) as max_key
    from t2, t3
    where t2.c1 = t3.c1
    group by t2.c1, t2.c2, t2.c3
    order by t2.c1, t2.c2) v
where
  t1.c1 = v.c1(+) and
  t1.c3 = 1
; 

@stat

-- but this is pushable
select /*+ gather_plan_statistics */
  t3.c1, v.c3
from
  t3, 
  (select t1.c1, t2.c2, t1.c3 
   from t1, t2
   where t1.c1 = t2.c1
   --group by t1.c1, t2.c2, t1.c3  -- this part is the problem!!!
   order by 1, 2
   ) v
where
  t3.c1 = v.c1(+) and
  t3.c3 = 1
; 

@stat




-- union(all) join pred push
-- this is not pushable
select /*+ gather_plan_statistics  */
  t1.c1, v.c3
from
  t1, 
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat


-- but this works!!
select /*+ gather_plan_statistics push_pred(v) */
  t1.c1, v.c3
from
  t1, 
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat

-- what if we change the index to bitmap index
drop index t2_n1;
drop index t3_n1;
create bitmap index t2_n1 on t2(c1);
create bitmap index t3_n1 on t3(c1);

select /*+ gather_plan_statistics push_pred(v) */
  t1.c1, v.c3
from
  t1, 
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat

-- what about with use_nl hints?
select /*+ gather_plan_statistics 
        push_pred(v) leading(t1) use_nl(t1)  */
  t1.c1, v.c3
from
  t1, 
  (select c1, c3 from t2
   union all
   select c1, c3 from t3) v
where
  t1.c1 = v.c1 and
  t1.c3 = 1
; 

@stat

drop index t2_n1;
drop index t3_n1;
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);


-- this is not pushable(simple predicate pushing)
select /*+ gather_plan_statistics  */
  t1.c1, v.c3
from
  t1, 
  (select rownum as r, c1, c2, c3
    from t2 ) v
where
  t1.c1 = v.c1 and
  v.c3 = 1
; 

@stat


-- this is not join predicate-pushable
select /*+ gather_plan_statistics  */
  t3.c1, v.c2
from
  t3, 
  (select rownum as r, t1.c1 as c1, t2.c2 as c2, t1.c3 as c3
    from t1, t2
    where t1.c1 = t2.c1
    order by t1.c1, t2.c2) v
where
  t3.c1 = v.c1(+) and  
  t3.c3 = 1
; 

@stat
